USE [TNO_TuyenSinh]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_TruongDaiHoc_CreatedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[TruongDaiHoc] DROP CONSTRAINT [DF_TruongDaiHoc_CreatedDate]
END

GO

/****** Object:  Table [dbo].[TruongDaiHoc]    Script Date: 07/17/2013 08:50:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[TruongDaiHoc]
ADD [CreatedDate] [datetime] NULL
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TruongDaiHoc] ADD  CONSTRAINT [DF_TruongDaiHoc_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

CREATE TABLE [dbo].[DiemChuan](
	[ID_Truong] [nvarchar](50) NOT NULL,
	[MaNganh] [nvarchar](50) NOT NULL,
	[KhoiThi] [nvarchar](10) NOT NULL,
	[Diem] [float] NOT NULL,
 CONSTRAINT [PK_DiemChuan] PRIMARY KEY CLUSTERED 
(
	[ID_Truong] ASC,
	[MaNganh] ASC,
	[KhoiThi] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ChiTieu](
	[ID_Truong] [nvarchar](50) NOT NULL,
	[MaNganh] [nvarchar](50) NOT NULL,
	[ChiTieu] [int] NOT NULL,
 CONSTRAINT [PK_ChiTieu_1] PRIMARY KEY CLUSTERED 
(
	[ID_Truong] ASC,
	[MaNganh] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Index [IDX_Search]    Script Date: 07/18/2013 15:58:17 ******/
CREATE NONCLUSTERED INDEX [IDX_Search] ON [dbo].[ThiSinh_DaiHoc] 
(
	[ID_Truong] ASC,
	[HoTen] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_SORTDIEM]    Script Date: 07/25/2013 13:55:59 ******/
CREATE NONCLUSTERED INDEX [IX_SORTDIEM] ON [dbo].[ThiSinh_DaiHoc] 
(
	[TongDiem] DESC,
	[HoTen] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

--DECLARE @CurrentPage INT,
--	@TotalPages INT

--Exec [pro_GetTruongDaiHoc] '5610', 'BKA', 1, 20,@CurrentPage OUTPUT, @TotalPages OUTPUT
-- SELECT @CurrentPage, @TotalPages

CREATE PROCEDURE [dbo].[pro_GetTruongDaiHoc]
	@Id int,
	@PageSize INT,
	@CurrentPage INT OUTPUT,
	@TotalPages INT OUTPUT
AS
BEGIN

DECLARE @TotalRecordTruong INT,
		@TotalRecordNganh INT,
		@SeqNganh INT,
		@TongDiem FLOAT,
		@TotalRecords INT,
		@StartIndex INT,
		@EndIndex INT,
		@MaTruong NVARCHAR(50),
		@MaNganh NVARCHAR(50),
		@KhoiThi NVARCHAR(10)

SELECT @MaTruong = ID_Truong, @MaNganh = ISNULL(MaNganh, '')
FROM ThiSinh_DaiHoc WHERE ID = @ID
		
-- Truong
SELECT @TotalRecordTruong = COUNT(1) FROM ThiSinh_DaiHoc 
	WHERE ID_Truong = @MaTruong
SELECT TOP 1 A.*, B.Title INTO #Temp FROM (SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * FROM ThiSinh_DaiHoc 
    WHERE ID_Truong = @MaTruong) A LEFT JOIN TruongDaiHoc B ON A.ID_Truong = B.ID WHERE A.ID = @ID

-- Nganh
SELECT @TotalRecordNganh = COUNT(1) FROM ThiSinh_DaiHoc 
	WHERE ID_Truong = @MaTruong
    AND ISNULL(MaNganh, '') = @MaNganh
SELECT TOP 1 @SeqNganh = RowID, @KhoiThi = KhoiThi FROM (SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * FROM ThiSinh_DaiHoc 
    WHERE ID_Truong = @MaTruong AND ISNULL(MaNganh, '') = @MaNganh) A WHERE ID = @Id

SET @TongDiem = (SELECT TongDiem FROM #Temp)

-- Get Index
	SET @CurrentPage = CONVERT(INT, @SeqNganh - 1) / @PageSize
	SET @TotalRecords = @TotalRecordNganh
		
SET @TotalPages = CEIlING(CONVERT(FLOAT, @TotalRecords) / @PageSize)
SET @StartIndex = @CurrentPage * @PageSize + 1;
SET @EndIndex = CASE WHEN @TotalRecords > @StartIndex + @PageSize - 1 THEN @StartIndex + @PageSize - 1 ELSE @TotalRecords END

SELECT @SeqNganh AS SeqNganh

SELECT @TotalRecordTruong AS [HitCounter]
SELECT @TotalRecordNganh AS [HitCounter]
SELECT COUNT(1) AS [HitCounter] FROM ThiSinh_DaiHoc WHERE ID_Truong = @MaTruong AND TongDiem = @TongDiem
SELECT COUNT(1) AS [HitCounter] FROM ThiSinh_DaiHoc WHERE ID_Truong = @MaTruong AND TongDiem > @TongDiem
SELECT COUNT(1) AS [HitCounter] FROM ThiSinh_DaiHoc WHERE ID_Truong = @MaTruong AND ISNULL(MaNganh, '') = @MaNganh AND TongDiem = @TongDiem
SELECT COUNT(1) AS [HitCounter] FROM ThiSinh_DaiHoc WHERE ID_Truong = @MaTruong AND ISNULL(MaNganh, '') = @MaNganh AND TongDiem > @TongDiem

SELECT Diem FROM DiemChuan WHERE ID_Truong = @MaTruong AND ISNULL(MaNganh, '') = @MaNganh AND KhoiThi = @KhoiThi
SELECT ChiTieu FROM ChiTieu WHERE ID_Truong = @MaTruong
SELECT ChiTieu FROM ChiTieu WHERE ID_Truong = @MaTruong AND ISNULL(MaNganh, '') = @MaNganh

SELECT * FROM #Temp

SELECT * 
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * 
        FROM ThiSinh_DaiHoc WHERE ID_Truong = @MaTruong 
                AND ISNULL(MaNganh, '') = @MaNganh
) A WHERE RowID BETWEEN @StartIndex AND @EndIndex

DROP TABLE #Temp

END
GO